# encoding:gbk
import pandas as pd
import logging
import sys
from datetime import datetime
import sqlite3
import time

class SQLiteDB:
    def __init__(self, db_name):
        """初始化数据库连接"""
        self.db_name = db_name
        self.conn = None
        self.cursor = None

    def connect(self):
        """连接到SQLite数据库"""
        self.conn = sqlite3.connect(self.db_name)
        self.cursor = self.conn.cursor()
        print(f"Connected to {self.db_name} database.")

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.commit()
            self.conn.close()
            print(f"Connection to {self.db_name} closed.")

    def create_table(self, table_name, columns):
        """创建表格，传入表名和列的定义"""
        columns_str = ', '.join([f"{col} {dtype}" for col, dtype in columns.items()])
        query = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_str});"
        self.cursor.execute(query)
        print(f"Table '{table_name}' created or already exists.")

    def insert_data(self, table_name, data):
        """插入数据，data是一个字典形式的键值对"""
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['?' for _ in data])
        query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        self.cursor.execute(query, tuple(data.values()))
        print(f"Data inserted into '{table_name}'.")

    def fetch_all(self, query, params=()):
        """执行查询，返回所有结果"""
        self.cursor.execute(query, params)
        return self.cursor.fetchall()

    def fetch_one(self, query, params=()):
        """执行查询，返回一个结果"""
        self.cursor.execute(query, params)
        return self.cursor.fetchone()

    def update_data(self, table_name, data, condition):
        """更新数据，data是一个字典，condition是更新条件"""
        set_clause = ', '.join([f"{key} = ?" for key in data])
        query = f"UPDATE {table_name} SET {set_clause} WHERE {condition}"
        self.cursor.execute(query, tuple(data.values()))
        print(f"Data in '{table_name}' updated.")

    def delete_data(self, table_name, condition):
        """删除数据，condition是删除条件"""
        query = f"DELETE FROM {table_name} WHERE {condition}"
        self.cursor.execute(query)
        print(f"Data from '{table_name}' deleted.")

def init(ContextInfo):
    """初始化上下文参数"""
    ContextInfo.accID = '99057414'
    ContextInfo.stock_symbol = '513090.SH'
    ContextInfo.db_file = r'C:\logs\qmt.sqlite'
    ContextInfo.log_file = r'C:\logs\513090.log'
    ContextInfo.strategy_name = '网格513090'
    # ContextInfo.trace_price = 0
    ContextInfo.threshold = 500
    ContextInfo.turning_point_threshold = 0.003  # 
    ContextInfo.drop_threshold = 0.99   # 加拐点网格， 下跌1%买入
    ContextInfo.drop_threshold_1 = 0.995 # 正常网格
    ContextInfo.up_threshold = 1.01     # 加拐点网格， 下跌1%买入
    ContextInfo.up_threshold_1 = 1.005   # 正常网格
    ContextInfo.start = '20250227 09:30:00'
    ContextInfo.end = '20250228 15:00:00'
    # 设置日志
    logging.basicConfig(filename=ContextInfo.log_file, level=logging.INFO, format='%(asctime)s %(message)s')
    ContextInfo.base_price = get_base_price(ContextInfo)
    ContextInfo.min_price = ContextInfo.base_price
    ContextInfo.max_price = ContextInfo.base_price
    ContextInfo.net_grid_data = get_net_grid_data(ContextInfo)

def get_base_price(ContextInfo):
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    # query = "SELECT price FROM base_price where stock = '513090.SH'"
    query = "SELECT price FROM base_price where stock = ?"
    result = db.fetch_one(query,(ContextInfo.stock_symbol,))
    # print(float(result[0]))
    db.close()
    return float(result[0])

def update_base_price(ContextInfo):
    # 更新基准价
    deals = get_trade_detail_data(ContextInfo.accID, 'STOCK', 'DEAL', ContextInfo.strategy_name)
    if deals:
        ContextInfo.base_price = deals[-1].m_dPrice
    
    # 保存基准价到数据库
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    update_data = {'price': ContextInfo.base_price}
    # condition = "stock = '513090.SH'"
    # db.update_data('base_price', update_data, condition)
    condition = f"stock = '{ContextInfo.stock_symbol}'"
    db.update_data('base_price', update_data, condition)
    db.close()

def get_net_grid_data(ContextInfo):
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    query = f"SELECT * FROM '{ContextInfo.stock_symbol}'"
    result = db.fetch_all(query)
    db.close()
    return result

def turning_point_trigger(ContextInfo):
    bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d%H%M%S')
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    current_price = round(tick_data[ContextInfo.stock_symbol]['lastPrice'],3)
    # ----------------------下跌行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) < ContextInfo.drop_threshold:
        logging.info(f"----下跌行情：最新价：{current_price} 基准价：{ContextInfo.base_price} 最低价：{ContextInfo.min_price}")
        if current_price < ContextInfo.min_price:
            ContextInfo.min_price = current_price
            logging.info(f"-- 持续下跌， 更新最低价：{ContextInfo.min_price}")
        elif current_price > ContextInfo.min_price * (1 + ContextInfo.turning_point_threshold):
            logging.info(f"-- 下跌反弹，最新价:{current_price} > 最低价:{ContextInfo.min_price} + 最低价:{ContextInfo.min_price} * {ContextInfo.turning_point_threshold}")
            execute_trade(ContextInfo, current_price)  # 调用交易函数
            ContextInfo.min_price = current_price
            ContextInfo.max_price = current_price
            # ContextInfo.base_price = current_price
    # ----------------------上涨行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) > ContextInfo.up_threshold:
        logging.info(f"----上涨行情：最新价：{current_price} 基准价：{ContextInfo.base_price} 最高价：{ContextInfo.max_price}")
        if current_price > ContextInfo.max_price:
            ContextInfo.max_price = current_price
            logging.info(f"--持续上涨， 更新最高价：{ContextInfo.max_price}")
        elif current_price < ContextInfo.max_price * (1 - ContextInfo.turning_point_threshold):
            logging.info(f"--上涨跳水，最新价:{current_price} < 最高价:{ContextInfo.max_price} - 最高价:{ContextInfo.max_price} * {ContextInfo.turning_point_threshold}")
            execute_trade(ContextInfo, current_price)  # 调用交易函数
            ContextInfo.min_price = current_price
            ContextInfo.max_price = current_price
            # ContextInfo.base_price = current_price

def normal_trigger(ContextInfo):
    bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d%H%M%S')
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    current_price = round(tick_data[ContextInfo.stock_symbol]['lastPrice'],3)
    # ----------------------下跌行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) <= ContextInfo.drop_threshold_1:
        execute_trade(ContextInfo, current_price)  # 调用交易函数
        ContextInfo.min_price = current_price
        ContextInfo.max_price = current_price
            # ContextInfo.base_price = current_price
    # ----------------------上涨行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) > ContextInfo.up_threshold:
        execute_trade(ContextInfo, current_price)  # 调用交易函数
        ContextInfo.min_price = current_price
        ContextInfo.max_price = current_price

def execute_trade(ContextInfo, current_price):
    """处理交易逻辑"""
    excel_quantity = [item[1] for item in ContextInfo.net_grid_data if item[0] == current_price][0]
    position_data = get_holdings(ContextInfo.accID, 'STOCK')
    current_quantity = position_data[ContextInfo.stock_symbol]['持仓量']
    logging.info(f"-- 交易流程：当前持仓量 {current_quantity}  表格持仓量 {excel_quantity}")
    print(f"-- 交易流程：当前持仓量 {current_quantity}  表格持仓量 {excel_quantity}")
    # 表格持仓量 > 当前持仓量，买入
    if excel_quantity > current_quantity and excel_quantity - current_quantity > ContextInfo.threshold:
        account_info = get_account(ContextInfo, ContextInfo.accID, 'STOCK')
        available_cash = account_info.get('可用金额', 0)
        trade_cost = current_price * (excel_quantity - current_quantity)
        if available_cash >= trade_cost:
            tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
            stk_price = tick_data[ContextInfo.stock_symbol]['askPrice'][1]
            stk_amount = round((excel_quantity - current_quantity)/100) * 100
            passorder(23, 1101, ContextInfo.accID, ContextInfo.stock_symbol, 11, stk_price, stk_amount, ContextInfo.strategy_name, 1, ContextInfo)
            # 更新基准价
            time.sleep(30)
            execute_cancel(ContextInfo)
            # deals = get_trade_detail_data(ContextInfo.accID, 'STOCK', 'DEAL', '网格_EXCEL交易_综合_实盘_SQLITE')
            # ContextInfo.base_price = deals[-1].m_dPrice
            update_base_price(ContextInfo)
            logging.info(f"--交易流程--\n买入价格 {ContextInfo.base_price} 买入数量 {stk_amount} (Excel {excel_quantity} - 持仓 {current_quantity})")
            print(f"--交易流程--\n买入价格 {ContextInfo.base_price} 买入数量 {stk_amount} (Excel {excel_quantity} - 持仓 {current_quantity})")
        else:
            logging.info(f"--交易流程--\n买入失败：可用资金 {available_cash} 不足，需 {trade_cost}")
            print(f"--交易流程--\n买入失败：可用资金 {available_cash} 不足，需 {trade_cost}")
    # 表格持仓量 < 当前持仓量，卖出
    if excel_quantity < current_quantity and current_quantity - excel_quantity > ContextInfo.threshold:
        tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
        stk_price = tick_data[ContextInfo.stock_symbol]['bidPrice'][1]
        stk_amount = round((current_quantity - excel_quantity)/100) * 100
        passorder(24, 1101, ContextInfo.accID, ContextInfo.stock_symbol, 11, stk_price, stk_amount, ContextInfo.strategy_name, 1, ContextInfo)
        # 更新基准价
        time.sleep(30)
        execute_cancel(ContextInfo)
        # deals = get_trade_detail_data(ContextInfo.accID, 'STOCK', 'DEAL', '网格_EXCEL交易_综合_实盘_SQLITE')
        # ContextInfo.base_price = deals[-1].m_dPrice
        update_base_price(ContextInfo)
        # update_base_price(ContextInfo, stk_price)
        logging.info(f"--交易流程--\n卖出价格 {ContextInfo.base_price} 卖出数量 {stk_amount} (持仓 {current_quantity} - Excel {excel_quantity})")
        print(f"--交易流程--\n卖出价格 {ContextInfo.base_price} 卖出数量 {stk_amount} (持仓 {current_quantity} - Excel {excel_quantity})")

def get_account(ContextInfo, accountid, datatype):
    """获取账户数据（占位实现，需替换为实际接口）"""
    accounts = get_trade_detail_data(accountid, datatype, 'account')  # 假设返回账户详情列表
    result = {}
    for dt in accounts:
        result['总资产'] = dt.m_dBalance
        result['净资产'] = dt.m_dAssureAsset
        result['总市值'] = dt.m_dInstrumentValue
        result['总负债'] = dt.m_dTotalDebit
        result['可用金额'] = dt.m_dAvailable
        result['盈亏'] = dt.m_dPositionProfit
    return result

def get_holdings(accountid, datatype):
    '''
    arg:
        accountid: 账户id, 
        datatype:
            'FUTURE':期货
            'STOCK':股票
            ......
    return:
    {股票名:{'手数':int, '持仓成本':float, '浮动盈亏':float, '可用余额':int}}
    '''
    PositionInfo_dict = {}
    resultlist = get_trade_detail_data(accountid, datatype, 'POSITION')
    for obj in resultlist:
        PositionInfo_dict[obj.m_strInstrumentID + '.' + obj.m_strExchangeID] = {
            '持仓量':obj.m_nVolume,
            '持仓成本':obj.m_dOpenPrice,
            '浮动盈亏':obj.m_dFloatProfit,
            '可用余额':obj.m_nCanUseVolume,
            '成交日期':obj.m_strOpenDate
        }
    return PositionInfo_dict

def execute_cancel(ContextInfo):
    ''' 对未成交的订单执行撤单操作 '''
    order_id = get_last_order_id(ContextInfo.accID, 'STOCK', 'ORDER', ContextInfo.strategy_name)
    can_cancel = can_cancel_order(order_id, ContextInfo.accID, 'STOCK')
    if can_cancel:
        cancel(order_id,ContextInfo.accID, 'STOCK')
        print("执行撤单")

def handlebar(ContextInfo):
    # test(ContextInfo)
    
    # 对未成交的订单执行撤单操作
    # order_list = get_trade_detail_data(ContextInfo.accID, 'stock', 'order', '网格513090')
    # for order in order_list:
        # result = cancel(order.m_strOrderSysID,ContextInfo.accID,'stock',ContextInfo)
        # if result:
            # print(f'撤单：撤单号{order.m_strOrderSysID}')
    
    # 判断最新价/昨日收盘价，在区间内正常网格交易，超出区间使用拐点网格交易
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    if tick_data[ContextInfo.stock_symbol]['lastPrice']/tick_data[ContextInfo.stock_symbol]['lastClose'] < 1.01 and tick_data[ContextInfo.stock_symbol]['lastPrice']/tick_data[ContextInfo.stock_symbol]['lastClose'] > 0.99:
        normal_trigger(ContextInfo)
    else:
        turning_point_trigger(ContextInfo)

def test(ContextInfo):
    bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d')
    bar_time1 = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d%H%M%S')
    
    passorder(23, 1101, ContextInfo.accID, ContextInfo.stock_symbol, 11, 12, 10000, ContextInfo.strategy_name, 1, ContextInfo)
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    dat = get_net_grid_data(ContextInfo)
    print(tick_data)
    





